﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ModelLayer;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CinemaDbLib
{
	public class SeatDB
	{
		private Connection connection;
		private RoomDB roomDB;

		public SeatDB()
		{
			connection = new Connection();
			roomDB = new RoomDB();
		}

		public int getColCount(int roomId, int rowId)
		{
			string query = "SELECT MAX(col) FROM Seats WHERE roomId =" +roomId
				+ " AND row = " + rowId;
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			if (reader.Read())
				return reader.GetInt32(0);
			else
				return -1;
		}

		public int getRowCount(int roomId)
		{
			string query = "SELECT MAX(row) FROM Seats WHERE roomId =" + 
				roomId;
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			if (reader.Read())
				return reader.GetInt32(0);
			else
				return -1;
		}

		public List<Seat> getSeats()
		{
			return where("");
		}

		public Seat getSeatById(int id)
		{
			return singleWhere("id = " + id);
		}

		public List<Seat> getSeatsByRoom(int roomId)
		{
			return where("roomId = " + roomId);
		}

		private Seat singleWhere(String wClause)
		{
			Seat seat = null;

			string query = "SELECT * FROM Seats WHERE " + wClause;
			connection.Open();
			SqlDataReader reader = connection.Read(query);
			if (reader.Read())
			{
				seat = new Seat();
				seat.id = reader.GetInt32(0);
				seat.room = roomDB.getRoomById(reader.GetInt32(1));
				seat.row = reader.GetInt32(2);
				seat.col = reader.GetInt32(3);
			}

			reader.Close();
			connection.Close();

			return seat;
		}

		private List<Seat> where(String wClause)
		{
			List<Seat> seats = new List<Seat>();

			string query;
			if (!wClause.Equals(""))
				query = "SELECT * FROM Seats WHERE " + wClause;
			else
				query = "SELECT * FROM Seats";
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			while (reader.Read())
			{
				Seat seat = new Seat();
				seat.id = reader.GetInt32(0);
				seat.room = roomDB.getRoomById(reader.GetInt32(1));
				seat.row = reader.GetInt32(2);
				seat.col = reader.GetInt32(3);
				seats.Add(seat);
			}

			reader.Close();
			connection.Close();

			return seats;
		}

		public bool removeSeat(int id)
		{
			string query = "DELETE FROM Seats WHERE id=" + id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}

		public int insertSeat(Seat seat)
		{
			int id = connection.GetMaxId("Seats") + 1;
			string query = @"INSERT INTO Seats (id, roomId, row, col) "+
				"VALUES ( " + id + ", " + seat.room.id + ", " + seat.row
				+ ", " + seat.col + ")";
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			
			if (result == 1)
				return id;
			return -1;
		}

		public bool updateSeat(Seat seat)
		{
			string query = @"UPDATE Seats SET roomId=" + seat.room.id + 
				", row=" + seat.row + ", col=" + seat.col + 
				" WHERE id=" + seat.id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}
	}
}
